﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Assist;
using MySql.Data.MySqlClient;

namespace HYFamilyLibDataManager
{
	public class MySqlFamilyTable : IFamilyTable
	{
		public MySqlFamilyTable(FamilyLibDatabase db)
		{
			this.mDatabase = db;
		}

		public bool Add(DbCategory category, DbFamily family, DbTransaction tr)
		{
			bool result = false;
			byte[] value = null;
			if (family.Image != null)
			{
				MemoryStream memoryStream = new MemoryStream();
				family.Image.Save(memoryStream, ImageFormat.Jpeg);
				value = memoryStream.ToArray();
			}
			try
			{
				using (MySqlCommand mySqlCommand = ((MySqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					mySqlCommand.Transaction = (MySqlTransaction)tr;
					int num;
					if (family.Limit)
					{
						num = 1;
					}
					else
					{
						num = 0;
					}
					mySqlCommand.CommandText = string.Format("insert into FamilyTable(FamilyName,FamilyImg,ParentId,`Limit`,`Version`,Description,GUID,`Date`,KeyName,eID1,eID2,tag1,tag2) values ('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},{10},'{11}','{12}')", new object[]
					{
						family.Name,
						"?img",
						category.Guid.ToString(),
						num,
						family.Version,
						family.Description,
						family.Guid.ToString(),
						family.Date.ToString(),
						category.KeyName,
						family.ExData.Eid1,
						family.ExData.Eid2,
						family.ExData.Tag1,
						family.ExData.Tag2
					});
					MySqlParameter mySqlParameter = new MySqlParameter("?img", MySqlDbType.Blob);
					mySqlParameter.Value = value;
					mySqlCommand.Parameters.Add(mySqlParameter);
					mySqlCommand.ExecuteNonQuery();
					family.KeyName = category.KeyName;
					family.SetDatabase(this.mDatabase);
				}
				result = true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public int Count(Parent parent, CheckType checkType)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select GUID from FamilyTable where ParentId='{0}'", parent.Guid.ToString());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select GUID from FamilyTable where ParentId='{0}' and `Check`='{1}'", parent.Guid.ToString(), 1);
			}
			else
			{
				sqlStr = string.Format("select GUID from FamilyTable where ParentId='{0}' and `Check`!='{1}'", parent.Guid.ToString(), 1);
			}
			DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
			if (this.mDatabase.Connection.State == ConnectionState.Open)
			{
				this.mDatabase.Connection.Close();
			}
			return dataTable.Rows.Count;
		}

		public int Count(CheckType checkType)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select GUID from FamilyTable", Array.Empty<object>());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select GUID from FamilyTable where `Check`='{0}'", 1);
			}
			else
			{
				sqlStr = string.Format("select GUID from FamilyTable where `Check`!='{0}'", 1);
			}
			DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
			if (this.mDatabase.Connection.State == ConnectionState.Open)
			{
				this.mDatabase.Connection.Close();
			}
			return dataTable.Rows.Count;
		}

		public bool Update(DbFamily family)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			int num = 0;
			if (family.Limit)
			{
				num = 1;
			}
			int num2 = 0;
			if (family.Check)
			{
				num2 = 1;
			}
			bool result;
			try
			{
				using (MySqlCommand mySqlCommand = ((MySqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					mySqlCommand.CommandText = string.Format("update FamilyTable set FamilyName='{0}',ParentId='{1}',`Limit`='{2}',`Version`='{3}',Description='{4}',FamilyImg={5},KeyName='{6}',eID1={7},eID2={8},tag1='{9}',tag2='{10}',`Date`='{11}',`Check`='{12}',`Count`='{13}',`UseDate`='{14}' where GUID='{15}'", new object[]
					{
						family.Name,
						family.ParentId.ToString(),
						num,
						family.Version,
						family.Description,
						"?img",
						family.KeyName,
						family.ExData.Eid1,
						family.ExData.Eid2,
						family.ExData.Tag1,
						family.ExData.Tag2,
						family.Date.ToString("yyyy-MM-dd HH:mm:ss"),
						num2,
						family.Count,
						family.UseDate.ToString("yyyy-MM-dd HH:mm:ss"),
						family.Guid.ToString()
					});
					MySqlParameter mySqlParameter = new MySqlParameter("?img", MySqlDbType.Blob);
					byte[] value = null;
					if (family.Image != null)
					{
						MemoryStream memoryStream = new MemoryStream();
						family.Image.Save(memoryStream, ImageFormat.Jpeg);
						value = memoryStream.ToArray();
					}
					mySqlParameter.Value = value;
					mySqlCommand.Parameters.Add(mySqlParameter);
					if (mySqlCommand.ExecuteNonQuery() != -1)
					{
						if (this.mDatabase.Connection.State == ConnectionState.Open)
						{
							this.mDatabase.Connection.Close();
						}
						result = true;
					}
					else
					{
						if (this.mDatabase.Connection.State == ConnectionState.Open)
						{
							this.mDatabase.Connection.Close();
						}
						result = false;
					}
				}
			}
			catch (Exception ex)
			{
				string message = ex.Message;
				if (ex.Message.Contains("Duplicate entry"))
				{
					MessageBox.Show("族名称重复！");
				}
				this.mDatabase.Connection.Close();
				result = false;
			}
			return result;
		}

		public void ReDateByNow(DbFamily family)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			string arg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
			using (MySqlCommand mySqlCommand = ((MySqlConnection)this.mDatabase.Connection).CreateCommand())
			{
				mySqlCommand.CommandText = string.Format("update FamilyTable set `Date`='{0}' where GUID='{1}'", arg, family.Guid.ToString());
				mySqlCommand.ExecuteNonQuery();
			}
			if (this.mDatabase.Connection.State == ConnectionState.Open)
			{
				this.mDatabase.Connection.Close();
			}
		}

		public DbFamily this[Guid id]
		{
			get
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dataTable = this.mDatabase.ExecQuery("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where GUID='" + id.ToString() + "'");
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public DbFamily this[string name]
		{
			get
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dataTable = this.mDatabase.ExecQuery("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where FamilyName='" + name + "'");
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public List<DbFamily> this[Parent parent, CheckType checkType]
		{
			get
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}' ", parent.Guid.ToString());
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}' and Check='{1}'", parent.Guid.ToString(), 1);
				}
				else
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}' and Check=!'{1}'", parent.Guid.ToString(), 1);
				}
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				return this.DatatableToDbFamilyList(dt);
			}
		}

		private List<DbFamily> DatatableToDbFamilyList(DataTable dt)
		{
			List<DbFamily> list = new List<DbFamily>();
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					Image img = new Bitmap(new MemoryStream((byte[])dt.Rows[i]["FamilyImg"]));
					DbFamily dbFamily = new DbFamily(dt.Rows[i]["FamilyName"].ToString().Trim(), img, new Guid(dt.Rows[i]["GUID"].ToString()));
					dbFamily.Limit = Convert.ToBoolean(dt.Rows[i]["Limit"].ToString());
					dbFamily.Version = dt.Rows[i]["Version"].ToString().Trim();
					dbFamily.SetDate(Convert.ToDateTime(dt.Rows[i]["Date"].ToString()));
					dbFamily.Description = dt.Rows[i]["Description"].ToString().Trim();
					dbFamily.SetParentId(new Guid(dt.Rows[i]["ParentId"].ToString()));
					dbFamily.SetDatabase(this.mDatabase);
					dbFamily.KeyName = dt.Rows[i]["KeyName"].ToString();
					if (dt.Rows[i]["eID1"].ToString() != "")
					{
						dbFamily.ExData.Eid1 = Convert.ToInt32(dt.Rows[i]["eID1"].ToString());
					}
					if (dt.Rows[i]["eID2"].ToString() != "")
					{
						dbFamily.ExData.Eid2 = Convert.ToInt32(dt.Rows[i]["eID2"].ToString());
					}
					dbFamily.ExData.Tag1 = dt.Rows[i]["tag1"].ToString();
					dbFamily.ExData.Tag2 = dt.Rows[i]["tag2"].ToString();
					dbFamily.Check = (bool)dt.Rows[i]["Check"];
					dbFamily.Count = Convert.ToInt32(dt.Rows[i]["Count"].ToString());
					if (dt.Rows[i]["UseDate"].ToString() != "")
					{
						dbFamily.SetUseDate(Convert.ToDateTime(dt.Rows[i]["UseDate"].ToString()));
					}
					list.Add(dbFamily);
				}
			}
			return list;
		}

		public List<DbFamily> GetAll()
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			DataTable dt = this.mDatabase.ExecQuery("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable");
			if (this.mDatabase.Connection.State == ConnectionState.Open)
			{
				this.mDatabase.Connection.Close();
			}
			return this.DatatableToDbFamilyList(dt);
		}

		public bool Remove(DbFamily family)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			DbTransaction tr = this.mDatabase.StartTransaction();
			bool result;
			try
			{
				this.mDatabase.ExecuteCommand("delete from FamilyTable where GUID='" + family.Guid.ToString() + "'", tr);
				this.mDatabase.FamilyFileTable.Remove(family, tr);
				this.mDatabase.FamilyImgTable.Remove(family, tr);
				this.mDatabase.FamilyParamTable.Remove(family, tr);
				this.mDatabase.TypeTable.Drop(family, tr);
				this.mDatabase.CommitTransaction(tr);
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = true;
			}
			catch
			{
				this.mDatabase.RollbackTransaction(tr);
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = false;
			}
			return result;
		}

		public FileInfo GetFamilyFile(DbFamily family)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			string text = Assembly.GetExecutingAssembly().Location;
			int num = text.LastIndexOf("\\");
			text = text.Substring(0, num + 1);
			FileInfo result = null;
			DataTable dataTable = this.mDatabase.ExecQuery("select FamilyName,FamilyFile from FamilyTable where GUID='" + family.Guid.ToString() + "'");
			if (this.mDatabase.Connection.State == ConnectionState.Open)
			{
				this.mDatabase.Connection.Close();
			}
			if (dataTable != null)
			{
				byte[] pReadByte = (byte[])dataTable.Rows[0][1];
				string fileName = text + "\\HY_" + dataTable.Rows[0][0].ToString() + ".rfa";
				Assist.FileUtil.WriteFile(pReadByte, fileName);
				result = new FileInfo(fileName);
			}
			return result;
		}

		public List<DbFamily> SearchFamily(List<string> keys)
		{
			string text = "'%" + keys[0];
			for (int i = 1; i < keys.Count; i++)
			{
				text = text + "%" + keys[i] + "%";
			}
			text += "'";
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				string sqlStr = "select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where tag1 like " + text;
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				return this.DatatableToDbFamilyList(dt);
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public List<DbFamily> this[Parent parent, int start, int end, CheckType checkType]
		{
			get
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,`FamilyName`,`ParentId`,`Version`,`Date`,`Description`,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}' limit {1} offset {2}", parent.Guid.ToString(), end - start, start);
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}'  and `Check`='{1}' limit {2} offset {3}", new object[]
					{
						parent.Guid.ToString(),
						1,
						end - start,
						start
					});
				}
				else
				{
					sqlStr = string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where ParentId='{0}'  and `Check`=!'{1}' limit {2} offset {3}", new object[]
					{
						parent.Guid.ToString(),
						1,
						end - start,
						start
					});
				}
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				return this.DatatableToDbFamilyList(dt);
			}
		}

		public List<DbFamily> SearchFamily(string familyName, int start, int end)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery(string.Concat(new string[]
				{
					"select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where \r\n                                                     FamilyName like'%",
					familyName,
					"%' limit ",
					(end - start).ToString(),
					" offset ",
					start.ToString()
				}));
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public List<DbFamily> SearchFamily(string familyName)
		{
			if (this.mDatabase.Connection.State == ConnectionState.Closed)
			{
				this.mDatabase.Connection.Open();
			}
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where FamilyName like'%" + familyName + "%'");
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public int SearchFamilyCount(string familyName)
		{
			new List<DbFamily>();
			int result = 0;
			try
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dataTable = this.mDatabase.ExecQuery("select * from FamilyTable where FamilyName like'%" + familyName + "%'");
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				if (dataTable != null)
				{
					result = dataTable.Rows.Count;
				}
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime, int start, int end)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dt = this.mDatabase.ExecQuery(string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where \r\n                                                     Date BETWEEN '{0}' AND '{1}' limit {2} offset {3}", new object[]
				{
					startTime.ToString("yyyy-MM-dd"),
					endTime.ToString("yyyy-MM-dd"),
					(end - start).ToString(),
					start.ToString()
				}));
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dt = this.mDatabase.ExecQuery(string.Format("select FamilyImg,`Limit`,FamilyName,ParentId,`Version`,`Date`,Description,GUID,KeyName,eID1,eID2,tag1,tag2,`Check`,`Count`,`UseDate` from FamilyTable where Date BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		public int SearchFamilyCount(DateTime startTime, DateTime endTime)
		{
			new List<DbFamily>();
			int result = 0;
			try
			{
				if (this.mDatabase.Connection.State == ConnectionState.Closed)
				{
					this.mDatabase.Connection.Open();
				}
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select * from FamilyTable where Date BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
				if (dataTable != null)
				{
					result = dataTable.Rows.Count;
				}
			}
			catch
			{
				if (this.mDatabase.Connection.State == ConnectionState.Open)
				{
					this.mDatabase.Connection.Close();
				}
			}
			return result;
		}

		private FamilyLibDatabase mDatabase;
	}
}
